package DBLayer;
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBInvoice implements IFDBInvoice{
    private  Connection con;
    /** Creates a new instance of DBInvoice */
    public DBInvoice() {
      con = DBConnection.getInstance().getDBcon();
    }
    
  //Implements the methods from the interface
    // get all Invoices on a customer
    public ArrayList<Invoice> listAllInvoiceOnCustomer(boolean retriveAssociation)
    {
        return miscWhereCustomer("", retriveAssociation);
    }
    // get all invoices on an employee
    public ArrayList<Invoice> listAllInvoiceOnEmployee(boolean retriveAssociation)
    {
        return miscWhereEmployee("", retriveAssociation);
    }
    
    //get one Invoice having the InvoiceId
    public Invoice findInvoice(int InvoiceId, boolean retriveAssociation)
    {   String wClause = "  id = '" + InvoiceId + "'";
        return singleWhere(wClause, retriveAssociation);
    }
     //insert a new Invoice
    @Override
    public int insertInvoice(Invoice inv) throws Exception
    {  //call to get the next InvoiceId number
        int nextInvoiceId = DBGetMax.getMaxId("Select max(InvoiceId) from Invoice");
        nextInvoiceId = nextInvoiceId + 1;
        System.out.println("next InvoiceId = " +  nextInvoiceId);
  
       int rc = -1;
	   String query="INSERT INTO Invoice(InvoiceId, paymentDate, totalPrice)  VALUES('"+
	  		nextInvoiceId  + "','"  +
		    inv.getPaymentDate()  + "','"  +
            inv.getTotalPrice() + "','";
                     

       System.out.println("insert : " + query);
      try{ // insert new Invoice +  dependent
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Invoice ikke oprettet");
          throw new Exception ("Invoice is not inserted correct");
       }
       return(rc);
    }

	
	
	
	
	//private methods
    //michWere is used whenever we want to select more than one Invoice
		 
	private ArrayList<Invoice> miscWhereCustomer(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Invoice> list = new ArrayList<Invoice>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the Invoice from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Invoice invObj = new Invoice();
		 invObj = buildInvoice(results);	
                 list.add(invObj);	
		}//end while
                 stmt.close();     
                 if(retrieveAssociation)
                 {  IFDBSalesOrder dbSalesOrder = new DBSalesOrder();
                 //The supervisor and department is to be build as well
                     for(Invoice invObj : list)
                     {
                    	 invObj.addSalesOrderArray(dbSalesOrder.findAllSalesOrdersOnCustomer(invObj.getInvoiceId()));
                     }
                 }//end if   
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	// this method i used when we want to select more than one employee
	private ArrayList<Invoice> miscWhereEmployee(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Invoice> list = new ArrayList<Invoice>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the Invoice from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Invoice invObj = new Invoice();
		 invObj = buildInvoice(results);	
                 list.add(invObj);	
		}//end while
                 stmt.close();     
                 if(retrieveAssociation)
                 {  IFDBSalesOrder dbSalesOrder = new DBSalesOrder();
                 //The supervisor and department is to be build as well
                     for(Invoice invObj : list)
                     {
                    	 invObj.addSalesOrderArray(dbSalesOrder.findAllSalesOrdersOnEmployee(invObj.getInvoiceId()));
                     }
                 }//end if   
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//Singelwhere is used when we only select one Invoice 	
	private Invoice singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Invoice invObj = new Invoice();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the Invoice from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            invObj = buildInvoice(results);
                            //assocaition is to be build
                            stmt.close();
                            if(retrieveAssociation)
                            {   
                            	IFDBSalesOrder dbSalesOrder = new DBSalesOrder();
                            	invObj.addSalesOrderArray(dbSalesOrder.findAllSalesOrdersOnCustomer(invObj.getInvoiceId()));                            }
			}
                        else{ //no Invoice was found
                            invObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return invObj;
	}
	
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT InvoiceId, paymentDate, totalPrice FROM Invoice";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build an Invoice object
	private Invoice buildInvoice(ResultSet results)
      {   Invoice invObj = new Invoice();
          try{ // the columns from the table invlayee  are used
        	  	invObj.setInvoiceId(results.getInt("InvoiceId"));
                invObj.setPaymentDate(results.getString("paymentDate"));
                invObj.setTotalPrice(results.getDouble("totalPrice"));
          }
         catch(Exception e)
         {
             System.out.println("error in building the Invoice object");
         }
         return invObj;
      }
	// this method is used to find all salesorders on an invoice
	 public ArrayList<SalesOrder> findAllInvoiceSalesOrders(int invoiceId)
     {
         IFDBSalesOrder dbSale = new DBSalesOrder();
         ArrayList<SalesOrder> result = dbSale.findAllSalesOrdersOnCustomer(invoiceId);
         return result;
     }
	 // this method is used to delete an invoice with the given id
	 public int deleteInvoice(int invoiceId) {
			int rc = -1;
			PreparedStatement pstmt = null;
			String delete = "delete from Invoice where id = ?";
			System.out.println(delete);
			try {
				pstmt = con.prepareStatement(delete);
				pstmt.setInt(1, invoiceId);
				rc = pstmt.executeUpdate();
			} catch (SQLException sqlE) {
				System.out.println("SQL Error");
				System.out.println(sqlE.getMessage());
			} catch (Exception e) {
				e.getMessage();
			}
			return rc;
 
}  

}
    

